﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.ComponentModel;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;


namespace WebApplication10.App_Code
{
    [DataObject]
    public class CoverLetter
    {
              [DataObjectField(true, true)]
        public int CoverLetterID { get; set; }
        public string Title { get; set; }
        public string Description { get; set; }
        public string Institution { get; set; }
        public string Course { get; set; }
        public string Grade { get; set; }

        [DataObjectMethod(DataObjectMethodType.Select, true)]
        public static CoverLetter GetCoverLetterByID(string CoverLetterID)
        {
            SqlConnection conn = new SqlConnection();
            CoverLetter cl = null;

            try
            {
                conn = new SqlConnection();
                string connstr = ConfigurationManager.ConnectionStrings["BlueSearchDBString"].ToString();
                conn.ConnectionString = connstr;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "select * from CoverLetter where CoverLetterID=@CoverLetterID ";
                comm.Parameters.AddWithValue("@CoverLetterID", CoverLetterID);
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    cl = new CoverLetter();
                    cl.CoverLetterID = (int)dr["CoverLetterID"];
            
                    cl.Title = (string)dr["Title"];
                    cl.Description = (string)dr["Description"];
                    cl.Institution = (string)dr["Institution"];
                    cl.Course = (string)dr["Course"];
                    cl.Grade = (string)dr["Grade"];

                }
                dr.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
            return cl;
        }

        [DataObjectMethod(DataObjectMethodType.Select, true)]
        public static List<CoverLetter> GetCoverLetters()
        {
            List<CoverLetter> results = new List<CoverLetter>();
            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection();
                string connstr = ConfigurationManager.ConnectionStrings["BlueSearchDBString"].ToString();
                conn.ConnectionString = connstr;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "Select * from CoverLetter";
                SqlDataReader dr = comm.ExecuteReader();

                while (dr.Read())
                {
                    CoverLetter cl = new CoverLetter();
                    cl.CoverLetterID = (int)dr["CoverLetterID"];
          
                    cl.Title = (string)dr["Title"];
                    cl.Description = (string)dr["Description"];
                    cl.Institution = (string)dr["Institution"];
                    cl.Course = (string)dr["Course"];
                    cl.Grade = (string)dr["Grade"];
                }
                dr.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
            return results;
        }

        [DataObjectMethod(DataObjectMethodType.Update, true)]
        public static void UpdateCoverLetter(CoverLetter cl)
        {
            SqlConnection conn = new SqlConnection();

            try
            {
                conn = new SqlConnection();
                string connstr = ConfigurationManager.ConnectionStrings["BlueSearchDBString"].ToString();
                conn.ConnectionString = connstr;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText =
                    "Update CoverLetter set Title=@Title, Description=@Description, Institution=@Institution,Course=@Course, Grade=@Grade where CoverLetterID=@CoverLetterID";
               
                comm.Parameters.AddWithValue("@Title", cl.Title);
                comm.Parameters.AddWithValue("@Description", cl.Description);
                comm.Parameters.AddWithValue("@Institution", cl.Institution);
                comm.Parameters.AddWithValue("@Course", cl.Course);
                comm.Parameters.AddWithValue("@Grade", cl.Grade);
                comm.Parameters.AddWithValue("@CoverLetterID", cl.CoverLetterID);
                int rowsAffected = comm.ExecuteNonQuery();
            
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
        }
        [DataObjectMethod(DataObjectMethodType.Delete, true)]
        public static void DeleteCoverLetter(CoverLetter cl)
        {
            SqlConnection conn = new SqlConnection();

            try
            {
                conn = new SqlConnection();
                string connstr = ConfigurationManager.ConnectionStrings["BlueSearchDBString"].ToString();
                conn.ConnectionString = connstr;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText =
                    "Delete from CoverLetter WHERE CoverLetterID=@CoverLetterID";
                comm.Parameters.AddWithValue("@CoverLetterID", cl.CoverLetterID);
                int rowsAffected = comm.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
        }
        [DataObjectMethod(DataObjectMethodType.Insert, true)]
        public static int InsertCompany(CoverLetter cl)
        {
            SqlConnection conn = null;
            int newCoverLetterID = 0;

            try
            {
                conn = new SqlConnection();
                string connstr = ConfigurationManager.ConnectionStrings["BlueSearchDBString"].ToString();
                conn.ConnectionString = connstr;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "Insert into CoverLetter ( Title, Description,Institution,Course,Grade) VALUES ( @Title, @Description, @Institution, @Course, @Grade)";
           
                comm.Parameters.AddWithValue("@Title", cl.Title);
                comm.Parameters.AddWithValue("@Description", cl.Description);
                comm.Parameters.AddWithValue("@Institution", cl.Institution);
                comm.Parameters.AddWithValue("@Course", cl.Course);
                comm.Parameters.AddWithValue("@Grade", cl.Grade);
                int rowsAffected = comm.ExecuteNonQuery();
                comm.CommandText = "select @@IDENTITY as newCoverLetterID";
                SqlDataReader dr = comm.ExecuteReader();
                dr.Read();
                newCoverLetterID = int.Parse(dr["NewCoverLetterID"].ToString());
                dr.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
            return newCoverLetterID;
        }


        public static int retrieveCoverLetter(int CoverLetterID)
        {
            SqlConnection conn = null;
            int results = 0;
            try
            {
                conn = new SqlConnection();
                string connstr = ConfigurationManager.ConnectionStrings["BlueSearchDBString"].ToString();
                conn.ConnectionString = connstr;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                SqlDataReader dr = comm.ExecuteReader();
                comm.Connection = conn;
                comm.CommandText = "select count(Title) as count from CoverLetter";
                while (dr.Read())
                {
                    results = (int)dr["count"];
                }

            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
            return results;


        }
    }
}